OpenEdge Data Management:<br />SQL Reference Array data types

Release 10.1A: OpenEdge Data Management:
SQL Reference

Array data types

The ARRAY data type is a composite data value that consists of zero or more elements of a specified data type (known as the element type). VARARRAY data type allows its element values to exceed the declared size when the total size is limited by SQL width.
Syntax
data_type ARRAY[int] | VARARRAY[int] 
data_type
The data type of the array. This is also known as the element type.

Supported data types are: BINARY, BIT, CHAR, VARCHAR, DATE, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, NUMERIC, REAL, SMALLINT, TIME, TIMESTAMP, TIMESTAMP_TZ, TINYINT, and VARBINARY.
[int]
An unsigned integer, indicating the array’s maximum element size.
Example

In this example, table TBL is created. TBL has two columns: column C1 is an array of up to 3 elements, all of them type int and column C2 is a variable-sized array of up to 4 elements, all of them type varchar:
CREATE TABLE TBL (C1 int ARRAY[3], C2 varchar(5) VARARRAY[4]); 
The size of any element in C2 can be up to 20 characters (5*4) with a total size of 20 characters.

Notes

OpenEdge SQL limits an array’s size. The array’s size must be an integer between 1 and 9999.

Array columns and element references cannot be indexed because:

You cannot define a UNIQUE key with columns of type ARRAY.

You cannot define a PRIMARY key with columns of type ARRAY.

You cannot define a FOREIGN key with columns of type ARRAY.

Array columns and element references cannot be used in GROUP BY clauses.

ARRAY element reference

An element reference allows you to access a specific element of an array. It operates on two arguments: the first must evaluate to an array and the second must evaluate to an integer. The integer refers to the ordinal position of the element in the array (the first element in the array is element number one, the second is element number two, and so on).
Example

In this example, the fourth element of the array column named array_column is returned:
SELECT array_column[4] FROM TBL; 
Default value for ARRAY columns

When creating array columns, you can specify a default value.
Example

The result returned from this example would be 10;10;10. Since no value is specified for array column C2 when inserting values, the default value is used:
CREATE TABLE tbl (C1 int, C2 int ARRAY[3] default ’10’); 
INSERT INTO tbl (C1) VALUES (1); 
SELECT C2 FROM tble WHERE C1 = 1; 
Note: The default value is applicable only at the column level. This means that if fewer values are specified when executing an insert statement, the default will not be used to fill up the rest of the array elements. Instead, NULL is used.

Assignment

When an array is assigned to an array target, the assignment is done one element at a time. Two arrays are assignable if their element’s data types are mutually assignable. This means:

When an array is taken from SQL data to be assigned to an array target, if the number of elements in the source array equals the maximum number of elements in the target array, the assignment is straightforward. The value of each element of the source is assigned to the corresponding element of the target.

If the maximum number of elements in the target array is less than the number of elements in the source array, then an error is returned.

If the maximum number of elements in the target array is greater than the number of elements in the source array, the assignment of each of the source element values to the target elements occurs and the rest of the target elements will be assigned values of NULL.

Example
CREATE TABLE TBL (C1 int, C2 int ARRAY[3]); 
INSERT into TBL values (1, ’111;222;333’); 
UPDATE TBL SET C2 = ’777;888;999’; 
Comparison

OpenEdge SQL provides two scalar comparison operators: = and <>. Two arrays are comparable if their element data types are mutually comparable. During comparison, the elements are compared pair-wise in element order. Two arrays are equal if:

They both have the same number of elements.

Each pair of elements is equal.

Two arrays are not equal if:

They do not have the same number or elements.

At least one pair of elements is not equal.